using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data.SqlClient;
using System.Reflection;

namespace RikMigrations.Providers
{
	public class MssqlProvider : DbProvider
	{
		#region Basic Commands
		protected override DbConnection CreateNewConnection()
		{
			DbConnection retVal;
			retVal = new SqlConnection(ConnectionString);
			retVal.Open();
			return retVal;
		}

		protected override DbParameter CreateParameter(int i, object value)
		{
			return new SqlParameter("@" + i, value);
		}
		#endregion

		#region Introspection
		public override bool TableExists(string name)
		{
			return ExecuteScalar("SELECT 1 FROM sysobjects WHERE xtype='u' AND name=@0", name) != null;
		}

        public override bool ViewExists(string name)
        {
            return ExecuteScalar("SELECT 1 FROM sysobjects WHERE xtype='v' AND name=@0", name) != null;
        }

        public override bool StoredProcedureExists(string name)
        {
            return ExecuteScalar("SELECT 1 FROM information_schema.routines where routine_name = @0", name) != null;
        }

		public override bool ColumnExists(string tableName, string name)
		{
			return ExecuteScalar("SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @0 AND COLUMN_NAME = @1", tableName, name) != null;
		}

		public override List<ForeignKey> FindForeignKeys(Table table)
		{
			List<ForeignKey> retVal = new List<ForeignKey>();
			using (DbDataReader reader = ExecuteReader(@"SELECT
CONSTRAINT_NAME = FK.CONSTRAINT_NAME,
COLUMN_NAME = FK_COLS.COLUMN_NAME,
REFERENCED_TABLE_NAME = PK.TABLE_NAME,
REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
AND FK.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.CONSTRAINT_TYPE = 'PRIMARY KEY'
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME
WHERE FK.TABLE_NAME = @0", table.Name))
			{
				while (reader.Read())
					retVal.Add(new ForeignKey(reader.GetString(0), table, reader.GetString(1), reader.GetString(2), reader.GetString(3)));
			}
			return retVal;
		}
		#endregion

		#region Migration Methods
		public override void DropTable(string name)
		{
			ExecuteNonQuery("DROP TABLE [" + name + "]");
		}

        public override void DropView(string name)
        {
            ExecuteNonQuery("Drop view [" + name + "]");
        }
		#endregion

		public override int GetModuleVersion(string moduleName)
		{
			InitialiseDb();
			object o = ExecuteScalar("SELECT CurrentVersion FROM ModuleVersions WHERE ModuleName = @0", moduleName);
			return o == null ? 0 : (int)o;
		}

		public override void SetModuleVersion(string moduleName, int version)
		{
			InitialiseDb();
			object o = ExecuteScalar("SELECT CurrentVersion FROM ModuleVersions WHERE ModuleName = @0", moduleName);
			if (o == null)
				ExecuteNonQuery("INSERT INTO ModuleVersions (ModuleName, CurrentVersion) VALUES (@0, @1)", moduleName, version);
			else
				ExecuteNonQuery("UPDATE ModuleVersions SET CurrentVersion = @0 WHERE ModuleName = @1", version, moduleName);
		}

		#region Commands
		public override Command GetCreateTableCommand(Table table)
		{
			return new MssqlCreateTableCommand(table);
		}

        public override Command GetCreateViewCommand(View view)
        {
            return new MssqlCreateViewCommand(view);
        }

		public override Command GetAddColumnCommand(Column column)
		{
			return new MssqlAddColumnCommand(column);
		}

		public override Command GetDropColumnCommand(Table table, string columnName)
		{
			return new MssqlDropColumnCommand(table,  columnName);
		}

        public override Command GetCreateSPCommand(StoredProcedure sp)
        {
            return new MssqlCreateStoredProcedureCommand(sp);
        }

        public override Command GetInsertCommand(string tableName, object objectToInsert, bool IsIdentityInsert)
        {
            return new MssqlInsertDataCommand(tableName, objectToInsert, IsIdentityInsert);
	    }



		#region Table Commands
		class MssqlCreateTableCommand : Command
		{
			Table table;

			public MssqlCreateTableCommand(Table table)
			{
				this.table = table;
			}

			public override void Execute(DbProvider db)
			{
				try
				{
					MssqlProvider msDb = db as MssqlProvider;
                        
					string sql = "CREATE TABLE [" + table.Name + "] (";
					foreach (Column col in table.Columns)
						sql += msDb.GetColumnSql(col) + ",\r\n";
					foreach (Constraint constraint in table.Constraints)
						sql += Providers.MssqlProvider.GetConstraintSql(constraint) + ",\r\n";
					sql = sql.Substring(0, sql.Length - 3);
					sql += ")";
					db.ExecuteNonQuery(sql);
				}
				catch (Exception e)
				{
					throw new Exception("Error creating table: " + table.Name, e);
				}
			}
		}
		#endregion

        #region Insert Command

        class MssqlInsertDataCommand : Command
        {
            private readonly string _tableName;
            private readonly object _objectToInsert;
            private readonly bool _isIdentityInsert;

            public MssqlInsertDataCommand(string tableName, object objectToInsert, bool isIdentityInsert)
            {
                _tableName = tableName;
                _objectToInsert = objectToInsert;
                _isIdentityInsert = isIdentityInsert;
            }

            public override void Execute(DbProvider db)
            {
                if (_isIdentityInsert)
                    db.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT [{0}] On", _tableName));

                db.ExecuteNonQuery(CreateInsertCommandString(_tableName, _objectToInsert));

                if (_isIdentityInsert)
                    db.ExecuteNonQuery(String.Format("SET IDENTITY_INSERT [{0}] Off", _tableName));
            }

            private string CreateInsertCommandString(string tableName, object objectToInsert)
            {
                string columnNames = "";
                string values = "";
                foreach (PropertyInfo property in objectToInsert.GetType().GetProperties())
                {
                    if (property.GetType() == typeof(int))
                    {
                        values += property.GetValue(objectToInsert, null) + ",";
                    }
                    else
                    {
                        values += String.Format("'{0}',", property.GetValue(objectToInsert, null));
                    }
                    columnNames += property.Name + ",";
                }

                return String.Format("INSERT INTO [{0}] ({1}) VALUES ({2})", tableName, columnNames.TrimEnd((',')), values.TrimEnd((',')));
            }
        }

	    #endregion

        #region View Commands
        class MssqlCreateViewCommand : Command
        {
            private View view;

            public MssqlCreateViewCommand(View view)
            {
                this.view = view;
            }

            public override void Execute(DbProvider db)
            {
                try
                {
                    // check if scripted or custom generated
                    if (!String.IsNullOrEmpty(view.Script))
                        db.ExecuteScalar(String.Format("Create view [dbo].[{0}] as {1}", view.Name, view.Script));
                    else 
                        Log.WriteLine("Skipping creation of View: {0}", view.Name);
                } catch (Exception e)
                {
                    throw new Exception("Error creating view: " + view.Name + "\r\n" + String.Format("Create view dbo.{0} as {1}", view.Name, view.Script) + "\r\n", e);
                }
            }
        }
        #endregion

        #region Column Commands
        internal class MssqlAddColumnCommand : Command
		{
			Column column;

			public MssqlAddColumnCommand(Column column)
			{
				this.column = column;
			}

			public override void Execute(DbProvider db)
			{
				MssqlProvider msDb = db as MssqlProvider;
				string sql = msDb.GetColumnSql(column);
				db.ExecuteNonQuery("ALTER TABLE " + column.Table.Name + " ADD " + sql);
			}
		}

        internal class MssqlCreateStoredProcedureCommand : Command
        {
            private StoredProcedure sp;

            public MssqlCreateStoredProcedureCommand(StoredProcedure sp)
            {
                this.sp = sp;
            }

            public override void Execute(DbProvider db)
            {
                try
                {
                    // check if scripted or custom generated
                    if (!String.IsNullOrEmpty(sp.Script))
                        db.ExecuteNonQuery(sp.Script);
                    else
                        Log.WriteLine("Skipping creation of Stored Procedure: {0}", sp.Name);
                }
                catch (Exception e)
                {
                    throw new Exception("Error creating Stored Procedure: " + sp.Name, e);
                }
            }
        }

	    internal class MssqlDropColumnCommand : Command
		{
			Table table;
			string name;

			public MssqlDropColumnCommand(Table table, string name)
			{
				this.table = table;
				this.name = name;
			}

			public override void Execute(DbProvider db)
			{
				DropAllConstraints(db);
				db.ExecuteNonQuery(string.Format("ALTER TABLE {0} DROP COLUMN {1}", table.Name, name));
			}

			void DropDefaultConstraints(DbProvider db)
			{
				List<string> constraints = new List<string>();
				using (DbDataReader reader = db.ExecuteReader(@"SELECT name FROM sys.default_constraints AS a
									WHERE (OBJECT_NAME(parent_object_id) = @0) AND (parent_column_id = (SELECT column_id FROM sys.columns WHERE (object_id = a.parent_object_id) AND (name = @1)))",
												table.Name, name))
				{
					while (reader.Read())
						constraints.Add(reader.GetString(0));
				}
				DropConstraints(db, constraints);
			}

			void DropAllConstraints(DbProvider db)
			{
				List<string> other = new List<string>();
				List<string> primary = new List<string>();
				using (DbDataReader reader = db.ExecuteReader(@"SELECT a.CONSTRAINT_NAME, CONSTRAINT_TYPE
					FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a
					INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME
					WHERE a.TABLE_NAME = @0 AND COLUMN_NAME = @1", table.Name, name))
				{
					while (reader.Read())
					{
						switch (reader.GetString(1))
						{
							case "PRIMARY KEY":
								primary.Add(reader.GetString(0));
								break;
							default:
								other.Add(reader.GetString(0));
								break;
						}
					}
				}
				DropConstraints(db, other);
				DropConstraints(db, primary);
				DropDefaultConstraints(db);
			}

			void DropConstraints(DbProvider db, List<string> constraintNames)
			{
				foreach (string constraint in constraintNames)
				{
					Log.WriteLine("Dropping {0}", constraint);
					db.ExecuteNonQuery(string.Format("ALTER TABLE {0} DROP CONSTRAINT {1}", table.Name, constraint));
				}
			}
		}
		#endregion

		#region Foreign Key Commands
		public override Command GetAddForeignKeyCommand(ForeignKey key)
		{
			return new CommandBase(delegate(DbProvider db) {
				db.ExecuteNonQuery(
					string.Format("ALTER TABLE [{0}] ADD CONSTRAINT [{1}] FOREIGN KEY ({2}) REFERENCES [{3}]([{4}]) ON DELETE {5} ON UPDATE {6}",
					key.Table.Name, key.Name, key.Column, key.ForeignTable, key.ForeignColumn, GetCascadeSql(key.DeleteCascadeOption), GetCascadeSql(key.UpdateCascadeOption)));
			});
		}

		string GetCascadeSql(CascadeOptions options)
		{
			switch (options)
			{
				case CascadeOptions.NoAction:
					return "NO ACTION";
				case CascadeOptions.Cascade:
					return "CASCADE";
				case CascadeOptions.SetNull:
					return "SET NULL";
				case CascadeOptions.SetDefault:
					return "SET DEFAULT";
				default:
					throw new ArgumentOutOfRangeException();
			}
		}
		#endregion

		#region Column Commands
		public override Command GetRenameTableCommand(Column column, string newName)
		{
			return new CommandBase(delegate(DbProvider db) {
				db.ExecuteNonQuery(
					string.Format("sp_rename '[{0}].[{1}]', '{2}', 'COLUMN'", column.Table.Name, column.Name, newName));
			});
		}
		#endregion

		#endregion

		#region Utilities
		public void DeleteAllTables()
		{
			ExecuteNonQuery(@"WHILE EXISTS(SELECT [name] FROM sys.tables WHERE [type] = 'U')
				BEGIN
				DECLARE @table_name varchar(50)
				DECLARE table_cursor CURSOR FOR SELECT [name] FROM sys.tables WHERE [type] = 'U'
				OPEN table_cursor
				FETCH NEXT FROM table_cursor INTO @table_name
				WHILE @@FETCH_STATUS = 0
				BEGIN
				BEGIN TRY
				EXEC ('DROP TABLE [' + @table_name + ']')
				PRINT 'Dropped Table ' + @table_name
				END TRY
				BEGIN CATCH END CATCH
				FETCH NEXT FROM table_cursor INTO @table_name
				END
				CLOSE table_cursor
				DEALLOCATE table_cursor
				END");
		}

		public static string GetSqlType(Type type, int length, bool isBlob, bool isUnicode)
		{
			if (type == typeof(bool))
				return "bit";
			else if (type == typeof(Guid))
				return "uniqueidentifier";
			else if (type == typeof(string))
			{
				string prefix = "";
				if (isUnicode)
					prefix = "n";
				if (isBlob)
					return prefix + "text";
				if (length > 8000 || (isUnicode && length > 4000))
					return prefix + "varchar(max)";
				else
					return prefix + "varchar(" + length + ")";
			}
			else if (type == typeof(char))
			{
				if (isUnicode)
					return "nchar";
				else
					return "char";
			}
			else if (type == typeof(byte))
				return "tinyint";
			else if (type == typeof(short))
				return "smallint";
			else if (type == typeof(int))
				return "int";
			else if (type == typeof(long))
				return "bigint";
			else if (type == typeof(double))
				return "float";
			else if (type == typeof(float))
				return "real";
			else if (type == typeof(DateTime))
				return "datetime";
			else if (type == typeof(decimal))
				return "decimal"; // TODO: Add support for precision and scale
			else if (type == typeof(byte[]))
			{
				if (isBlob)
					return "image";
				else
				{
					if (length > 8000)
						return "varbinary(max)";
					else
						return "varbinary(" + length + ")";
				}
			}
			else
				throw new NotImplementedException(string.Format("RikMigrations currently has no mapping of '{0}' for MS-SQL", type));
		}

		public static Type GetClrType(string sqlType)
		{
			switch (sqlType.ToLower())
			{
				case "tinyint":
					return typeof(byte);
				case "smallint":
					return typeof(short);
				case "int":
					return typeof(int);
				case "bigint":
					return typeof(long);

				case "float":
					return typeof(double);
				case "real":
					return typeof(float);

				case "smallmoney":
				case "money":
				case "numeric":
				case "decimal":
					return typeof(decimal);

				case "bit":
					return typeof(bool);
				case "uniqueidentifier":
					return typeof(Guid);

				case "varchar":
				case "nvarchar":
				case "text":
				case "ntext":
					return typeof(string);

				case "char":
				case "nchar":
					return typeof(char);

				case "smalldatetime":
				case "datetime":
					return typeof(DateTime);

				case "varbinary":
				case "image":
					return typeof(byte[]);

				default:
					throw new NotImplementedException(string.Format("RikMigrations currently has no mapping of '{0}' for MS-SQL", sqlType));
			}
		}

		public string GetColumnSql(Column column)
		{
			string sql = string.Format("[{0}] {1}", column.Name, column.SqlType ?? GetSqlType(column.Type, column.Length, column.IsBlob, column.IsUnicode));
			if (column.IsPrimaryKey)
				sql += " PRIMARY KEY";
			if (!column.AllowNull)
				sql += " NOT NULL";
			if (column.IsUnique)
				sql += " UNIQUE";
			if (column.DefaultValue != null)
				sql += " DEFAULT(" + GetLiteral(column.Type, column.DefaultValue) + ")";
			if (column.IsAutoGenerated)
				sql += " IDENTITY(1,1)";
			if (column.ForeignKey != null)
				sql += " REFERENCES " + column.ForeignKey.Table + "(" + column.ForeignKey.Column + ")";
			if (column.ExtraOptions != null)
				sql += " " + column.ExtraOptions;
			return sql;
		}

		public static string GetConstraintSql(Constraint constraint)
		{
			string sql;
			switch (constraint.Type)
			{
				case ConstraintType.PrimaryKey:
					sql = string.Format("CONSTRAINT [{0}] PRIMARY KEY (", constraint.Name);
					break;
				case ConstraintType.Unique:
					sql = string.Format("CONSTRAINT [{0}] UNIQUE (", constraint.Name);
					break;
				default:
					throw new NotImplementedException();
			}
			foreach (string col in constraint.Columns)
				sql += col + ",";
			return sql.Substring(0, sql.Length - 1) + ")";
		}
		#endregion
	}
}
